<?
// 3-18-11 Modfied publishChanges + markUnpublished to accept tablename argument
// 3-15-11 Added function fileExists() to print proper image + thumb after upload
// 3-11-11 Modified to accommodate BenBakerPhoto.com "Preview Mode"
// 12-29-09 Changed fileNameThumb to exclude non alphanumeric chars
require_once("global_vars.php");

$date = date("m-d-y g:i:s A T"); //Get the date and time.

$r = "------------------------------------------- TIMESTAMP: $date<br/>";
$r .= "GET args : <br/>".print_r($_GET, true)."<br/>";
$r .= "POST args : <br/>".print_r($_POST, true)."<br/>";
$myfile = getcwd() . "/ajaxLog.html";
$fh = fopen($myfile, 'a') or die("can't open file");
fwrite($fh, $r);
fclose($fh);

$tableName = $_GET['tablename'];
$id = $_GET['id'];

global $recordId;
$recordId = $_GET['recordId'];

$field = $_GET['field'];
global $varName;
$varName = $_GET['varName'];
$parent_tablename = $_GET['parentTable'];
$child_tablename = $_GET['child_tablename'];
$child_fieldname = $_GET['child_fieldname'];
$link_tablename = $_GET['link_tablename'];
$currentValue = $_GET['currentValue'];
$parentId = $_GET['parent_recordID'];

$fileName = $_GET['fileName'];
global $AJAXtask;
$AJAXtask = $_GET['AJAXtask'];
global $currFileType;
$currFileType = strtoupper($_GET['currFileType']);
global $isThumb;
$isThumb = $_GET['isThumb'];

//##### jCrop vars parsed here
$xCoord = $_GET['xCoord'];
$yCoord = $_GET['yCoord'];
$width = $_GET['width'];
$height = $_GET['height'];
$targetWidth = $_GET['targetWidth'];
$targetHeight = $_GET['targetHeight'];

require_once("database.php");
include "io_library.php";

switch ($AJAXtask) {
	// !deleteRecord
	case "deleteRecord":
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$SQLdelete = " DELETE FROM $tableName WHERE id = $id ";
		mysql_db_query($db_name, $SQLdelete, $SQLconnect);
		// Mark changes as unsaved
		$tableName = str_replace("_preview", "", $tableName);
		$SQLchangesQuery = "UPDATE bb_changes SET unsavedChanges = 1 WHERE tablename='$tableName'";
		$db->query($SQLchangesQuery);

		// Mark the non-preview record as deleted
		$SQLmarkDeletedQuery = "UPDATE $tableName SET markDeleted = 1 WHERE id = $id";
		echo "SQLmarkDeletedQuery : $SQLmarkDeletedQuery";
		$db->query($SQLmarkDeletedQuery);
		break;
	// !deleteFile
	case "deleteFile":
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$SQLfileToDelete = "SELECT $field AS fileToDelete FROM $tableName WHERE id = $id ";
		echo $SQLfileToDelete;
		$SQLexecute = mysql_db_query($db_name, $SQLfileToDelete, $SQLconnect);
		while($rowArray = mysql_fetch_array($SQLexecute,MYSQL_ASSOC)) {
			$file = $rowArray["fileToDelete"];
			$relativePath = str_replace($http_root, "", $file);
			$fullPath = $server_root . $relativePath;
			unlink($fullPath);
		}
		break;
	// !upload	
	case "upload":
		$filePathArray = explode("/",$fileName);
		$fileWithEXT = $filePathArray[sizeof($filePathArray) - 1];
		$fileName = fileExists($fileWithEXT);
		$fileUploadString = fileUploadDisplay($fileName);
		echo $fileUploadString;
		break;
	// !crop
	case "crop":
		$targ_w = $targetWidth;
		$targ_h = $targetHeight;
		$jpeg_quality = 90;
		$png_quality = 1;
		$filePathArray = explode("/",$fileName);
		$fileWithEXT = $filePathArray[sizeof($filePathArray) - 1];
		$filePathArray[sizeof($filePathArray) - 1] = "";
		$fileNameArray = explode(".",$fileWithEXT);
		$fileEXT = array_pop($fileNameArray);
		$newFileArray = array();
		$newFileArray = $fileNameArray;
		$origFileBase = rawurlencode(implode(".",$fileNameArray));
		$newFileBase = rawurlencode(implode("_",$newFileArray));
		$fileNameArray[sizeof($fileNameArray) - 2] = $fileBase;
		$thumbSuffix = "_thumb";
		
		$safeFilePath =  implode("/", $filePathArray) . $origFileBase . ".$fileEXT";
		$newFileBase = preg_replace("/[^a-zA-Z0-9\s]/", "", $newFileBase);
		$thumbFilePath = $newFileBase . $thumbSuffix . ".$fileEXT";

		switch (strtoupper($fileEXT)) {
			case "JPEG":
			case "JPG":
				$img_r = imagecreatefromjpeg($safeFilePath);
				$dst_r = ImageCreateTrueColor( $targ_w, $targ_h );
				imagecopyresampled($dst_r,$img_r,0,0,$xCoord,$yCoord,$targ_w,$targ_h,$width,$height);
				imagejpeg($dst_r,$server_root . $thumbs_path . $thumbFilePath,$jpeg_quality);
				break;
			case "PNG":
				$img_r = imagecreatefrompng($safeFilePath);
				//echo "thumbFilePath : " . $thumbFilePath . "\n";
				$dst_r = ImageCreateTrueColor( $targ_w, $targ_h );
				imagecopyresampled($dst_r,$img_r,0,0,$xCoord,$yCoord,$targ_w,$targ_h,$width,$height);
				imagepng($dst_r,$server_root . $thumbs_path . $thumbFilePath,$png_quality);
				break;
			case "GIF":
				$img_r = imagecreatefromgif($safeFilePath);
				$dst_r = ImageCreateTrueColor( $targ_w, $targ_h );
				imagecopyresampled($dst_r,$img_r,0,0,$xCoord,$yCoord,$targ_w,$targ_h,$width,$height);
				imagegif($dst_r,$server_root . $thumbs_path . $thumbFilePath);
				break;
			default:
				echo "ERROR: Unrecognized File Type to Crop...";
		}	
	
		imagedestroy($dst_r);
		//echo "safe file path:".$safeFilePath;
		echo $http_root . $thumbs_path . $thumbFilePath;
		break;
	// !populateDropDown
	case "populateDropDown":
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$SQLdropDown = "SELECT $child_fieldname AS fieldname, id FROM $child_tablename";
		$SQLexecute = mysql_db_query($db_name, $SQLdropDown, $SQLconnect);
		while($rowArray = mysql_fetch_array($SQLexecute,MYSQL_ASSOC)) {
			$fieldName = $rowArray["fieldname"];
			$id = $rowArray["id"];
			echo "<option value=\"$id\"";
			if ($id == $currentValue)
				echo "selected=\"selected\" ";
			echo ">$fieldName</option>";
		}
		break;
	// !saveNewOpt
	case "saveNewOpt":
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$SQLnewOpt = "INSERT INTO " . $_GET['child_tablename'] . " (".$_GET['child_fieldname'].") VALUES ('".$_GET['currentValue']."')";
		$SQLexecute = mysql_db_query($db_name, $SQLnewOpt, $SQLconnect);
		echo mysql_insert_id();
		break;
	// !saveGroupItem
	case "saveGroupItem":
		$groupItemDataArray = array();
		foreach ($_GET as $key => $value) {
			if (($key != "child_tablename") && ($key != "id") && ($key != "_") && ($key != "AJAXtask")) 
				$groupItemDataArray[] = "$key = '$value'";
		}
		$groupItemDataStr = implode(", ", $groupItemDataArray);
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		echo $SQLsaveGroupItem = "UPDATE " . $_GET['child_tablename'] . " SET $groupItemDataStr WHERE id = '" . $_GET['id'] ."'";
		$SQLexecute = mysql_db_query($db_name, $SQLsaveGroupItem, $SQLconnect);

		break;
	// !saveNewGroupItem
	case "saveNewGroupItem":
		$groupItemKeyArray = array();
		$groupItemValueArray = array();
		foreach ($_GET as $key => $value) {
			if (($key != "child_tablename") && ($key != "id") && ($key != "_") && ($key != "AJAXtask")) {
				$groupItemKeyArray[] = "$key";
				$groupItemValueArray[] = "'$value'";
			}
		}
		$groupItemKeyStr = implode(", ", $groupItemKeyArray);
		$groupItemValueStr = implode(", ", $groupItemValueArray);
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		echo $SQLnewGroupItem = "INSERT INTO " . $_GET['child_tablename'] . " ($groupItemKeyStr) VALUES ($groupItemValueStr)";
		//INSERT INTO images (image,image_thumb,Table3_id) VALUES ('ddd','$image_thumb','$Table3_id')
		$SQLexecute = mysql_db_query($db_name, $SQLnewGroupItem, $SQLconnect);

		break;
	// !deleteGroupItem
	case "deleteGroupItem":
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$child_tableName = $_GET["child_tablename"];
		$SQLdelete = " DELETE FROM $child_tableName WHERE id = $id ";
		echo $SQLdelete;
		mysql_db_query($db_name, $SQLdelete, $SQLconnect);
		break;
	// !autocompleteSearch
	case "autocompleteSearch":
		$parentId = $_GET['parent_recordID'];
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		//$SQLautocompleteSearch = "SELECT child_id FROM $link_tablename WHERE parent_id = '$parentId'";
		$SQLautocompleteSearch = "SELECT $child_tablename.$child_fieldname AS fieldname, $child_tablename.id AS id FROM $child_tablename WHERE $child_tablename.id NOT IN (SELECT child_id FROM $link_tablename WHERE parent_id = '$parentId')";
		//$SQLautocompleteSearch = "SELECT $child_tablename.$child_fieldname AS fieldname, $child_tablename.id AS id, $link_tablename.parent_id AS parent_id FROM $child_tablename LEFT JOIN $link_tablename ON  $child_tablename.id = $link_tablename.child_id WHERE $link_tablename.parent_id = '$parentId' ORDER BY fieldname";  //hard code these vars in the extra params
		//echo $SQLautocompleteSearch . "\n";
		$SQLexecute = mysql_db_query($db_name, $SQLautocompleteSearch, $SQLconnect);
		$items = array();
		//SELECT * FROM $child_tablename LEFT JOIN $linktablename ON $linktablename.child_id = $child_tablename.id WHERE $linktablename.child_id IS NULL
		$q = strtolower($_GET["q"]);
		if (!$q) return;
		while($rowArray = mysql_fetch_array($SQLexecute,MYSQL_ASSOC)) {
			$fieldName = $rowArray["fieldname"];
			$id = $rowArray["id"];
			$parent_id = $rowArray["parent_id"];
			$items[$fieldName] = $id;
			//echo $parent_id . "\n";
		}
		//print_r($items);
		foreach ($items as $key=>$value) {
			if (strpos(strtolower($key), $q) !== false) {
				//echo htmlspecialchars("$key|$value\n");
				echo strip_tags("$key|$value\n");
			}
		}
		break;
	// !autocompleteSave
	case "autocompleteSave":
		$child_recordID = $_GET['child_recordID'];
		$SQLautocompleteSave .= "INSERT INTO " . $_GET['link_tablename'] . " (child_id, parent_id) VALUES ($child_recordID, ". $_GET['parent_recordID'] .")";
		//echo $SQLautocompleteSave;

		/*$child_recordIDs = explode(",", $_GET['child_recordIDs']);
				foreach ($child_recordIDs as $child_recordID) {
					if($child_recordID != "")
						$SQLautocompleteSave .= "INSERT INTO " . $_GET['link_tablename'] . " (child_id, parent_id) VALUES ($child_recordID, ". $_GET['parent_recordID'] .");\n";
		}*/

		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		//INSERT INTO images (image,image_thumb,Table3_id) VALUES ('ddd','$image_thumb','$Table3_id')
		//echo "\n db_name: $db_name \n $SQLautocompleteSave: $SQLautocompleteSave"+
		//"\n SQLconnect: ($db_host , $db_username, $db_password" ;
		//echo $SQLautocompleteSave;
		$SQLexecute = mysql_db_query($db_name, $SQLautocompleteSave, $SQLconnect);
		$linkTableName = $_GET['link_tablename'];
		$childTableName = $_GET['child_tablename'];
		$parentId = $_GET['parent_recordID'];
		include $_GET['child_tablename'] . "_linkedtablelist.php";
		break;
	// !linkedTableRemove
	case "linkedTableRemove":
		$SQLlinkedTableRemove .= "DELETE FROM " . $_GET['linkedTableName'] . " WHERE child_id='" . $_GET['child_id']."' AND parent_id='" . $_GET['parent_id'] . "'";
		echo $SQLlinkedTableRemove;
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$SQLexecute = mysql_db_query($db_name, $SQLlinkedTableRemove, $SQLconnect);
		break;
	// !sortorderUpdate
	case "sortorderUpdate":
/*
		$serializedorder = $_GET['serializedOrder'];
		echo $serializedorder;
*/
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		foreach($_GET['order'] as $order => $itemid) :
			$sqlorderupdate = "UPDATE $tableName SET sort_order = '$order' WHERE id = '$itemid'";
			echo $sqlorderupdate . "\n";
			$SQLexecute = mysql_db_query($db_name, $sqlorderupdate, $SQLconnect);
			// Boucherie Events Flash Update Neato Hackzor
			if ( $tableName == "events") { // if we update the events sort_order, reprint text for Flash, yay!
				require_once("../eventsText.php");
			}
		endforeach;
		break;
	// !linkTableInsert
	case "linkTableInsert":
		$link_tablename = $_GET['linkTableName'];
		$parentId = $_GET['parentId'];
		$child_ids = $_GET['child_ids'];
		
		$SQLsortQuery = "SELECT MAX(sort_order) AS sort_order FROM $link_tablename WHERE parent_id = $parentId";
		$SQLsortResult = $db->query($SQLsortQuery);
		$sortArray = $db->fetch_array($SQLsortResult);
		$sort_order = htmlentities($sortArray["sort_order"]);
		$sort_order++;
		
		$SQLsaveQuery = "INSERT INTO $link_tablename (parent_id, child_id, sort_order) VALUES ";
		foreach ($child_ids as $childId) {
			$SQLvalues[] = "('$parentId','$childId', '$sort_order')";
			$sort_order++;
		}
		$SQLsaveQuery .= implode(', ', $SQLvalues);
		echo $SQLsaveQuery;
		$SQLresult = $db->query($SQLsaveQuery);
		break;
	// !linksortorderUpdate
	case "linksortorderUpdate":
		foreach($_GET['order'] as $order => $itemid) :
			$SQLorderUpdate = "UPDATE $link_tablename SET sort_order = '$order' WHERE child_id = '$itemid' AND parent_id = '$parentId'";
			echo $SQLorderUpdate . "\n";
			$SQLresult = $db->query($SQLorderUpdate);
		endforeach;
		// Mark changes as unpublished
		$parent_tablename = str_replace("_preview", "", $parent_tablename);
		$SQLchangesQuery = "UPDATE bb_changes SET unsavedChanges = 1 WHERE tablename='$parent_tablename'";
		echo $SQLchangesQuery;
		$db->query($SQLchangesQuery);
		echo "Marked unsaved!";
		break;
	// !publishChanges
	case "publishChanges":
		$SQLpreviewQuery = "SELECT * FROM ".$tableName."_preview";
		$SQLpreviewResult = $db->query($SQLpreviewQuery);
		while ($SQLpreviewRow = $db->fetch_array($SQLpreviewResult) ) {
			$sort_order = $SQLpreviewRow["sort_order"];
			$title = $SQLpreviewRow["title"];
			$image = $SQLpreviewRow["image"];
			$image_thumb = $SQLpreviewRow["image_thumb"];
			$section = $SQLpreviewRow["section"];
			$prevId = $SQLpreviewRow["id"];
			
			unset($columns);
			unset($newValues);
			unset($editValues);
			
			foreach ($SQLpreviewRow as $column => $val) {
				$columns[] = $column;
				$newValues[] = "'$val'";
				$editValues[] = "$column = '$val'"; 
			}
			
			$SQLpublishQuery = "SELECT * FROM ".$tableName." WHERE id=$prevId";
			$SQLpublishResult = $db->query($SQLpublishQuery);
			//echo "\nRaw Result : \n".print_r($SQLpreviewRow,true)."\n\n";
			if (mysql_affected_rows() > 0 ){
				//echo "\nID ($prevId) exists already. UPDATING, not inserting...";
				// UPDATE	current recordId in photos table
				
				$SQLeditQuery = "UPDATE ".$tableName." SET ";	
				$SQLeditQuery .= join(", ",$editValues); 
				$SQLeditQuery .= " WHERE id = '$prevId' ";
				$SQLeditResult = $db->query($SQLeditQuery);
				//echo "\n$SQLeditQuery\n\n";
			} else if (mysql_affected_rows() == 0) {
				//echo "\nID ($prevId) DOES NOT EXIST. INSERTING new record...";
				// INSERT new record to refelct new record from PREVIEW mode
				$SQLnewQuery = "INSERT INTO ".$tableName." (".join(', ',$columns).") VALUES (".join(', ',$newValues).")";
				//echo "\n$SQLnewQuery\n\n";
				$SQLnewResult = $db->query($SQLnewQuery);
			} else {
				echo "SQL ERROR in ajax.php where ajaxTask == 'publishChanges'.";
			}
		}
		
		// Find all records in the non-preview table designated for deletion.
		// Then delete the record and the file
		$SQLdeleteQuery = "SELECT * FROM $tableName WHERE markDeleted = 1";
		$SQLdeleteResult = $db->query($SQLdeleteQuery);
		while ($SQLdeleteArray = $db->fetch_array($SQLdeleteResult) ) {
			print_r($SQLdeleteArray);
			$deleteId = $SQLdeleteArray["id"];
			$deleteImage = $SQLdeleteArray["image"];
			$deleteImageThumb = $SQLdeleteArray["image_thumb"];

			// Unlink image
			$relativePath = str_replace($http_root, "", $deleteImage);
			$fullPath = $server_root . $relativePath;
			unlink($fullPath);
			// Unlink image_thumb
			$relativePath = str_replace($http_root, "", $deleteImageThumb);
			$fullPath = $server_root . $relativePath;
			unlink($fullPath);
			// Delete record on non-preview table
			$SQLdelete = " DELETE FROM $tableName WHERE id = $deleteId ";
			$db->query($SQLdelete);
		}
		// Mark changes as published
		$SQLchangesQuery = "UPDATE bb_changes SET unsavedChanges = 0 WHERE tablename='$tableName'";
		//echo $SQLchangesQuery;
		$db->query($SQLchangesQuery);
		echo "Changes Published!";
		break;
	// !markUnpublished
	case "markUnpublished":
		// Mark changes as unpublished
		$SQLchangesQuery = "UPDATE bb_changes SET unsavedChanges = 1 WHERE tablename='$tableName'";
		$db->query($SQLchangesQuery);
		echo "Marked unsaved!";
		break;
	default:
		echo "<h2>ERROR: AJAX task not supported...</h2>";
		break;
}





/*
	case "facelistSearch":
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		$SQLdropDown = "SELECT $child_fieldname AS fieldname, id FROM $child_tablename";  //hard code these vars in the extra params
		$SQLexecute = mysql_db_query($db_name, $SQLdropDown, $SQLconnect);
		$items = array();
		
		$q = strtolower($_GET["q"]);
		if (!$q) return;
		while($rowArray = mysql_fetch_array($SQLexecute,MYSQL_ASSOC)) {
			$fieldName = $rowArray["fieldname"];
			$id = $rowArray["id"];
			$items[$fieldName] = $id;
		}
		//print_r($items);
		foreach ($items as $key=>$value) {
			if (strpos(strtolower($key), $q) !== false) {
				//echo htmlspecialchars("$key|$value\n");
				echo strip_tags("$key|$value\n");
			}
		}
		break;
	case "facelistSave":
		$child_recordID = $_GET['child_recordID'];
		$SQLautocompleteSave .= "INSERT INTO " . $_GET['link_tablename'] . " (child_id, parent_id) VALUES ($child_recordID, ". $_GET['parent_recordID'] .")";

		/*$child_recordIDs = explode(",", $_GET['child_recordIDs']);
				foreach ($child_recordIDs as $child_recordID) {
					if($child_recordID != "")
						$SQLautocompleteSave .= "INSERT INTO " . $_GET['link_tablename'] . " (child_id, parent_id) VALUES ($child_recordID, ". $_GET['parent_recordID'] .");\n";
		}*/
		
/*
		$SQLconnect = mysql_connect($db_host,$db_username,$db_password);
		//INSERT INTO images (image,image_thumb,Table3_id) VALUES ('ddd','$image_thumb','$Table3_id')
		//echo "\n db_name: $db_name \n $SQLautocompleteSave: $SQLautocompleteSave \n SQLconnect: ($db_host , $db_username, $db_password" ;
		echo $SQLautocompleteSave;
		$SQLexecute = mysql_db_query($db_name, $SQLautocompleteSave, $SQLconnect);
		break;
*/

function fileExists($fileName) {
	//echo "filename = $fileName</br>";
	global $images_path, $audio_path, $movies_path, $server_root, $http_root,$relFilePath;

	$splitArray = explode(".",$fileName);
	$fileEXT = $splitArray[sizeof($splitArray) - 1];
	switch (strtoupper($fileEXT)) {
			case "MOV":
			case "MP4":
				$relFilePath = $movies_path;
				break;
			case "FLV":
				$relFilePath = $movies_path;
			//IMAGES
			case "JPG":
			case "JPEG":
			case "PNG":
			case "GIF":
				$relFilePath = $images_path;
				break;
			case "PDF":
				break;
			//AUDIO
			case "MP3":
			case "MP4":
			case "AIFF":
				$relFilePath = $audio_path;
				break;
			default:
				$relFilePath = "ERROR IN FILE PATH";
				break;
	}
	
	$filePath = "$server_root$relFilePath$fileName";
	

	if (file_exists($filePath)) {
		$r = "File [$fileName] exists. Appending name...<br/>";
		$myfile = getcwd() . "/ajaxLog.html";
		$fh = fopen($myfile, 'a') or die("can't open file");
		fwrite($fh, $r);
		fclose($fh);
		
		$fileExploded = explode(".", $fileName);
		$fileEXT = array_pop($fileExploded);
		$newFileName = implode(".", $fileExploded);
		$newFileName .= "_1.$fileEXT";
		$newFilePath = "$server_root$relFilePath$newFileName";
		if (file_exists($newFilePath)) {
			return fileExists($newFileName);
		} else {
			return $fileName;
		}
	} else {
		$r = "File [$fileName] is unique. Saving...<br/>";
		$myfile = getcwd() . "/ajaxLog.html";
		$fh = fopen($myfile, 'a') or die("can't open file");
		fwrite($fh, $r);
		fclose($fh);

		return $fileName;
	}	
} // end function fileExists

?>